﻿
-- ================================================
-- Remarks: Not part of standard Stored Proc
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Ivy_UserRoleAllocationSummary_GetAll')
	BEGIN
		DROP  Procedure  Ivy_UserRoleAllocationSummary_GetAll
	END

GO

CREATE PROCEDURE [Ivy_UserRoleAllocationSummary_GetAll] 
-- =============================================
-- Author:		<Ram pravesh kr>
-- Create date: <23/06/2016>
-- Description:	<Ivy_UserRoleAllocationSummary_GetAll>
-- =============================================
-- Add the parameters for the stored procedure here
	(
			@Sys_IsDeleted int = NULL, 
			@Sys_OrganizationID int = NULL 
	) 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT OFF;

	SELECT DISTINCT 
					[UserOrganizationRole].[UserKey],
					[UserOrganizationRole].[UserLoginID], 
					[User].[UserLoginIdType], 
					[Person].[PersonFirstName], 
					[Person].[PersonLastName], 
					[Person].[PersonPrimaryEmailID],
					[UserOrganizationRole].[UserOrganizationKey], 
					[UserOrganizationRole].[UserOrganizationName]
	FROM			[Person] RIGHT OUTER JOIN [User] 
	ON				[Person].[PersonKey] = [User].[UserPersonKey] LEFT OUTER JOIN UserOrganizationRole 
	ON				[User].[UserPersonKey] = [UserOrganizationRole].[UserKey]

	WHERE 
			( 
					[UserOrganizationRole].[Sys_IsDeleted] = ISNULL(@Sys_IsDeleted, [UserOrganizationRole].[Sys_IsDeleted]) AND
					[UserOrganizationRole].[Sys_OrganizationID] = ISNULL(@Sys_OrganizationID, [UserOrganizationRole].[Sys_OrganizationID]) 
			); 
END
GO
